﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Net.Mail;
using System.Timers;
using System.Security.Cryptography;

using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;

using OpenPop.Mime;
using OpenPop.Mime.Header;
using OpenPop.Pop3;
using OpenPop.Pop3.Exceptions;
using OpenPop.Common.Logging;

using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.Configuration;
using System.Net;
using System.Collections.Specialized;

using System.Text.RegularExpressions;

namespace DorisPoller {

    class DorisPOP3Poller {


        public class DorisUser {
            public String CustID { get; set; }
            public String Username {get; set; }
            public String Password {get; set; }
            public int Exists { get; set; }
        }

        public class TicketStatus {
            public int TicketID { get; set; }
            public String Service { get; set; }
            public String Priority { get; set; }
            public String Status { get; set; }
        }

        public class MAILConnection {
            public String ConnectionName { get; set; }
            public String Server { get; set; }
            public Boolean UseCredentials { get; set; }
            public String Username { get; set; }
            public String Password { get; set; }
            public int Port { get; set; }
            public Boolean UseSSL { get; set; }
            public Boolean Enabled { get; set; }
        }
	/// <summary>
	/// A very simple alphanumerical password generator. This class is free to use without restrictions.
	/// </summary>
	/// <example>
	/// // Alphanumeric password example
	/// int passwordLength = 20;
	/// AlphaNumericPasswordGenerator alphaNumeric = new AlphaNumericPasswordGenerator();
	/// string password = alphaNumeric.Generate(passwordLength);
	/// Console.Console.WriteLine(password);
	/// </example>
	public class AlphaNumericPasswordGenerator {
		/// <summary>
		/// Generates a password with the given character length.
		/// </summary>
		/// <param name="Length"></param>
		/// <returns></returns>
		public string Generate(int Length) {
			/* Alternative method
			Random random = new Random();
			return MD5Hash(random.Next(0,1000).ToString());
			*/

			if ( Length < 5 ) {
				Length = 5;
			}
			
			Random random = new Random();
			string password = MD5Hash( random.Next().ToString() ).Substring(0,10);
			string newPass = "";

			// Uppercase at random
			random = new Random();
			for (int i=0;i < password.Length;i++) {
				if ( random.Next(0,2) == 1 )
					newPass += password.Substring(i,1).ToUpper();
				else
					newPass += password.Substring(i,1);
			}

			return newPass;
		}

		private string MD5Hash(string Data) {
			MD5 md5 = new MD5CryptoServiceProvider();
			byte[] hash = md5.ComputeHash( Encoding.ASCII.GetBytes(Data) );

			StringBuilder stringBuilder = new StringBuilder();
			foreach( byte b in hash ) {
				stringBuilder.AppendFormat("{0:x2}", b);
			}
			return stringBuilder.ToString();
		}
	}


        public static DorisUser AddCustomer(String Email, String Fullname, int TicketPriorityID) {

            DorisUser doris = new DorisUser();

            //doris.UserClass UserClass = new doris.UserClass();
            /*
            // make the username
            String[] unpart = Fullname.Split(new char[] { ' ' });
            for (int i = 0; i < unpart.Count(); i++) {
                unpart[i] = unpart[i].Substring(0, 3);
            }*/

            // add user
            DorisMembershipProvider provider = new DorisMembershipProvider();
            NameValueCollection config = new NameValueCollection();
            config.Add("name","DorisMembershipProvider");
            config.Add("type","DorisMembershipProvider");
            config.Add("applicationName", "/");
            config.Add("connectionStringName", "DorisAdminConnection");

            provider.Initialize("DorisMembershipProvider", config);
            MembershipCreateStatus status = new MembershipCreateStatus();

            doris.CustID = provider.GetUserIDByEmail(Email);

            Console.WriteLine("user : " + doris.CustID);
            if (doris.CustID == "") {
                AlphaNumericPasswordGenerator alphaNumeric = new AlphaNumericPasswordGenerator();
                doris.Password = alphaNumeric.Generate(8);
                doris.Username = Email; // String.Join("", unpart);

                MembershipUser member = provider.CreateUser(doris.Username, doris.Password, Email, null, null, true, null, out status);
                doris.CustID = member.ProviderUserKey.ToString();
            } else {
                doris.Username = provider.GetUserNameByEmail(Email);
                doris.Password = null;
            }

            return doris;
        }

        public static TicketStatus GetTicketStatus(int TicketID) {
            TicketStatus output = new TicketStatus();
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
//                cmd.CommandText = "getTicketStatus";
                cmd.CommandText = "getTicketStatus";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new MySqlParameter("@in_ticketid", MySqlDbType.VarChar));
                cmd.Parameters["@in_ticketid"].Value = TicketID;
                cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;
                
                cmd.Parameters.Add(new MySqlParameter("@out_service", MySqlDbType.VarChar));
                cmd.Parameters["@out_service"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_priority", MySqlDbType.VarChar));
                cmd.Parameters["@out_priority"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_status", MySqlDbType.VarChar));
                cmd.Parameters["@out_status"].Direction = ParameterDirection.Output;
                
                cmd.ExecuteNonQuery();

                output.TicketID = TicketID;
                output.Service = cmd.Parameters["@out_service"].Value.ToString();
                output.Priority = cmd.Parameters["@out_priority"].Value.ToString();
                output.Status = cmd.Parameters["@out_status"].Value.ToString();
                
                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
            return output;
        }

        public static int AddTicket( String customerid, String title, int ticketpriorityid, String message) {
            int TicketID = 0;
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            try {
                MySqlCommand cmd = new MySqlCommand();
                conn.Open();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
                cmd.CommandText = "addTicket";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@in_title", title);
                cmd.Parameters["@in_title"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_userid", customerid);
                cmd.Parameters["@in_userid"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_ticketpriorityid", ticketpriorityid);
                cmd.Parameters["@in_ticketpriorityid"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_message", message);
                cmd.Parameters["@in_message"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@out_ticketid", MySqlDbType.Int32);
                cmd.Parameters["@out_ticketid"].Direction = ParameterDirection.Output;

                cmd.Parameters.AddWithValue("@out_ticketdataid", MySqlDbType.Int32);
                cmd.Parameters["@out_ticketdataid"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                TicketID = int.Parse(cmd.Parameters["@out_ticketid"].Value.ToString());
                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }

            return TicketID;
        }
        public static void AddTicketReply(int ticketid, String customerid, String title, int ticketpriorityid, String message) {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            try {
                MySqlCommand cmd = new MySqlCommand();
                conn.Open();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
                cmd.CommandText = "addTicketReply";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@in_ticketid", ticketid);
                cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_title", title);
                cmd.Parameters["@in_title"].Direction = ParameterDirection.Input;

//                cmd.Parameters.AddWithValue("@in_userid", customerid);
//                cmd.Parameters["@in_userid"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_ticketpriorityid", ticketpriorityid);
                cmd.Parameters["@in_ticketpriorityid"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_message", message);
                cmd.Parameters["@in_message"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_agentusername", null);
                cmd.Parameters["@in_agentusername"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_workminutes", null);
                cmd.Parameters["@in_workminutes"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_parentticketdataid", null);
                cmd.Parameters["@in_parentticketdataid"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@in_tickedassignedid", null);
                cmd.Parameters["@in_tickedassignedid"].Direction = ParameterDirection.Input;

                cmd.Parameters.AddWithValue("@out_ticketdataid", MySqlDbType.Int32);
                cmd.Parameters["@out_ticketdataid"].Direction = ParameterDirection.Output;

                Console.WriteLine("now querying");
                cmd.ExecuteNonQuery();
                Console.WriteLine("done");
                //TicketID = int.Parse(cmd.Paramseters["@out_ticketid"].Value.ToString());
                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }

//            return TicketID;
        }

        public static void mailAboutReply(String Subject, int TicketID, SmtpClient smtp, String from, DorisUser doris, TicketStatus data) {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            String Message = String.Empty;
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
                //                cmd.CommandText = "getTicketStatus";
                cmd.CommandText = "getConfigurationForAllUsers";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new MySqlParameter("@in_configuration_type", MySqlDbType.VarChar));
                cmd.Parameters["@in_configuration_type"].Direction = ParameterDirection.Input;
                cmd.Parameters["@in_configuration_type"].Value = "ReplyAll";

                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    using (MailMessage newMail = new MailMessage()) {
                        String MyBody = "";
                        newMail.From = new MailAddress(from);
                        newMail.To.Add(reader["Email"].ToString());
                        newMail.Subject = "Svar på ticket[" + TicketID + "]: " + Subject;
                        newMail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(MyBody, null, "text/html"));
                        smtp.Send(newMail);
                    }
                }

                cmd.Dispose();


            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }

        }
        public static void mailAboutNewMessage(String Subject, int TicketID, SmtpClient smtp, String from, DorisUser doris, TicketStatus data) {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            String Message = String.Empty;
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
                //                cmd.CommandText = "getTicketStatus";
                cmd.CommandText = "getConfigurationForAllUsers";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new MySqlParameter("@in_configuration_type", MySqlDbType.VarChar));
                cmd.Parameters["@in_configuration_type"].Direction = ParameterDirection.Input;
                cmd.Parameters["@in_configuration_type"].Value = "ReplyAll";

                MySqlDataReader reader = cmd.ExecuteReader();

                while (reader.Read()) {
                    using (MailMessage newMail = new MailMessage()) {
                        String MyBody = "";
                        newMail.From = new MailAddress(from);
                        newMail.To.Add(reader["Email"].ToString());
                        newMail.Subject = "Ny ticket[" + TicketID + "]: " + Subject;
                        newMail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(MyBody, null, "text/html"));
                        smtp.Send(newMail);
                    }
                }

                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }

        }
        public static Boolean SendTicketResponse(String sender, String email, String subject, String fullname, String message, DorisUser doris, TicketStatus data, bool reply) {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            String Message = String.Empty;
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
                //                cmd.CommandText = "getTicketStatus";
                cmd.CommandText = "getDefaultResponse";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new MySqlParameter("@out_message", MySqlDbType.Text));
                cmd.Parameters["@out_message"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                Message = HttpUtility.HtmlDecode(cmd.Parameters["@out_message"].Value.ToString());
                cmd.Dispose();


            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }


            Message = Message.Replace("[fullname]", fullname);
            Message = Message.Replace("[ticketid]", data.TicketID.ToString());
            Message = Message.Replace("[subject]", subject);
            Message = Message.Replace("[department]", data.Service);
            Message = Message.Replace("[priority]", data.Priority);
            Message = Message.Replace("[status]", data.Status);
            Message = Message.Replace("[url]","URL");
            Message = Message.Replace("[username]", doris.Username);
            Message = Message.Replace("[password]", doris.Password);

            try
            {
                string MyFrom = sender;
                string MyTo = email;
                string MySubject = "[Ticket:" + data.TicketID + "] " + subject;
                string MyBody = Message;

                MAILConnection smtp = FetchSMTP();
/*                string MySmtpMailServerName = "mail.broadpark.no";
                int MySmtpMailServerPort = 25; //25 is the default for SMTP

                SmtpClient MySmtpClient = new SmtpClient(MySmtpMailServerName, MySmtpMailServerPort);*/
                SmtpClient MySmtpClient = new SmtpClient(smtp.Server, smtp.Port);
                if (smtp.UseCredentials) {
                    MySmtpClient.Credentials = new NetworkCredential(smtp.Username, smtp.Password);
                }
                if (smtp.UseSSL) {
                    MySmtpClient.EnableSsl = true;
                }

                Console.WriteLine("Sender epost fra " + smtp.Server + ", " + smtp.Port + " identifisert med " + smtp.Username + " og " + smtp.Password);
//                MySmtpClient.Send(MyFrom, MyTo, MySubject, MyBody);
                
                /*
                 * Hent inn hvem som vil motta notifikasjon og ikke. FRITZ
                 * */
                if (reply) {
                    mailAboutReply(MySubject, data.TicketID, MySmtpClient, MyFrom, doris, data);
                } else {
                    mailAboutNewMessage(MySubject, data.TicketID, MySmtpClient, MyFrom, doris, data);
                }
/*
|                   1 | NewQuestions |
|                   2 | ReplyAll     |
|                   3 | ReplyMine    |
 */ 
// * MailConfiguration
                
                


                using (MailMessage newMail = new MailMessage()) {
                    newMail.From = new MailAddress(MyFrom);
                    newMail.To.Add(MyTo);
                    newMail.Subject = MySubject;
                    newMail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(MyBody, null, "text/html"));
                    MySmtpClient.Send(newMail);
                }

                Console.WriteLine("Message sent.");
            }
            catch (Exception MyError)
            {
                Console.WriteLine("An error has occurred: " + MyError.Message);
                return false;
            }
            Console.WriteLine("Done sending through smtp");
            return true;
        }
        public static int CheckForTicketID(String subject, String Email) {
            Match rMatch = Regex.Match(subject, @".*\[Ticket\:([0-9]+)\].*");
            String newEmail = String.Empty;

            if (!rMatch.Success)
                return 0;

            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            try {
                MySqlCommand cmd = new MySqlCommand();
                conn.Open();
                cmd.Connection = conn;

                cmd.CommandText = "getTicketPoller";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@in_ticketid", rMatch.Groups[1].Value);
                cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@out_ticketstatusid", MySqlDbType.Int32));
                cmd.Parameters["@out_ticketstatusid"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_status", MySqlDbType.VarChar));
                cmd.Parameters["@out_status"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_ticketpriorityid", MySqlDbType.Int32));
                cmd.Parameters["@out_ticketpriorityid"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_priority", MySqlDbType.VarChar));
                cmd.Parameters["@out_priority"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_title", MySqlDbType.VarChar));
                cmd.Parameters["@out_title"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_createddate", MySqlDbType.DateTime));
                cmd.Parameters["@out_createddate"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_updateddate", MySqlDbType.DateTime));
                cmd.Parameters["@out_updateddate"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_pendingdate", MySqlDbType.DateTime));
                cmd.Parameters["@out_pendingdate"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_locked", MySqlDbType.Int16));
                cmd.Parameters["@out_locked"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_userid", MySqlDbType.VarChar));
                cmd.Parameters["@out_userid"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_username", MySqlDbType.VarChar));
                cmd.Parameters["@out_username"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_email", MySqlDbType.VarChar));
                cmd.Parameters["@out_email"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                newEmail = cmd.Parameters["@out_email"].Value.ToString();

                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }

            if (newEmail == Email)
                return int.Parse(rMatch.Groups[1].Value);
            else
                return 0;
        }
        static void Main(string[] args) {
            Timer myTimer = new Timer();
            myTimer.Elapsed += new ElapsedEventHandler( FetchAndProcess );
            myTimer.Interval = 1000 * 10; // 30 second
            myTimer.Start();
  
            while ( Console.Read() != 'q' ) {
                ;    // do nothing...
            }
        }

        static void FetchAndProcess( object source, ElapsedEventArgs e) {
            List<MAILConnection> pop3s = FetchPOP3();

            for (int i = 0; i < pop3s.Count; i++) {
                List <Message> msgs = FetchAllMessages(pop3s[i].Server, pop3s[i].Port, pop3s[i].UseSSL, pop3s[i].Username, pop3s[i].Password);
                ProcessMessages(msgs, pop3s[i].Server, pop3s[i].Port, pop3s[i].UseSSL, pop3s[i].Username, pop3s[i].Password);

            }
        }

        static List<MAILConnection> FetchPOP3() {
            List<MAILConnection> pop3s = new List<MAILConnection>();
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
//                cmd.CommandText = "getTicketStatus";
                cmd.CommandText = "getAllEnabledPop3";
                cmd.CommandType = CommandType.StoredProcedure;

                MySqlDataReader reader = cmd.ExecuteReader();
                
                while (reader.Read()) {
                    MAILConnection pop3 = new MAILConnection();
                    pop3.ConnectionName = reader.GetValue(1).ToString();
                    pop3.Server = reader.GetValue(2).ToString();
                    pop3.Username = reader.GetValue(4).ToString();
                    pop3.Password = reader.GetValue(5).ToString();
                    pop3.Port = int.Parse(reader.GetValue(6).ToString());
                    pop3.UseSSL = (int.Parse(reader.GetValue(7).ToString()) == 1?true:false);
                    pop3.Enabled = (int.Parse(reader.GetValue(8).ToString()) == 1?true:false);

                    pop3s.Add(pop3);
                }

                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
            return pop3s;
        }

        static MAILConnection FetchSMTP() {
            MAILConnection smtp = new MAILConnection();
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["DorisConnection"].ConnectionString);
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
                //                cmd.CommandText = "getTicketStatus";
                cmd.CommandText = "getEnabledSmtp";
                cmd.CommandType = CommandType.StoredProcedure;

                MySqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read()) {
                    smtp.ConnectionName = reader.GetValue(1).ToString();
                    smtp.Server = reader.GetValue(2).ToString();
                    smtp.UseCredentials = (int.Parse(reader.GetValue(3).ToString()) == 1? true : false);
                    smtp.Username = reader.GetValue(4).ToString();
                    smtp.Password = reader.GetValue(5).ToString();
                    smtp.Port = int.Parse(reader.GetValue(6).ToString());
                    smtp.UseSSL = (int.Parse(reader.GetValue(7).ToString()) == 1 ? true : false);
                    smtp.Enabled = (int.Parse(reader.GetValue(8).ToString()) == 1 ? true : false);
                }

                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
            return smtp;
        }


        public static void ProcessMessages(List<Message> msgs, string hostname, int port, bool useSsl, string username, string password) {
            int TicketID;
            bool reply = false;
            Boolean bResponse;
            // The client disconnects from the server when being disposed
            for (int i=msgs.Count-1; i>=1; i--) {
                Message msg = msgs[i];
                String Subject = msg.Headers.Subject;
                RfcMailAddress rfc = msg.Headers.From;
                String Fullname = rfc.DisplayName;
                String Email = rfc.Address;
                String Body = String.Empty;
                MessagePart Mpart;
                
                Mpart = msg.FindFirstHtmlVersion();
                if (Mpart == null) {
                    Mpart = msg.FindFirstPlainTextVersion();
                    Body = Mpart.GetBodyAsText();
                } else {
                    Body = Mpart.GetBodyAsText();
                }

                Console.WriteLine("Subject: " + Subject + ", Name: " + Fullname + ", Email: " + Email );
                DorisUser doris = AddCustomer(Email, Fullname, 1);

                if ((TicketID = CheckForTicketID(Subject, Email)) == 0) { // check if exists
                    Console.WriteLine("AddTicket " + doris.CustID + ", " + Subject + ", 1, body");
                    TicketID = AddTicket(doris.CustID, Subject, 1, Body);
                    // hent opplysninger om department, priority, status, email og passord for bruekeren
                    /*
                        * Når man sender inn oppfølgingsmeldinger, så må man få svar tilbake med status osv. 
                        * 
                        */
                } else {
                    reply = true;
                    Console.WriteLine("******************* REPLY " + doris.CustID + ", " + Subject + ", 1, body");
                    AddTicketReply(TicketID,doris.CustID, Subject, 1, Body);
                }
                TicketStatus output = GetTicketStatus(TicketID);
                Console.WriteLine("Send ticket response " + Email + ", " + TicketID + ", " + Subject + ", " + Fullname + ", " + Email); // + ", " + output[0] + ", " + output[1] + ", " + output[2]);

                List<RfcMailAddress> addrs = msg.Headers.To;
                Console.WriteLine(addrs[0].Address);
                bResponse = SendTicketResponse(addrs[0].Address, Email, Subject, Fullname, Body, doris, output, reply);
                if (bResponse)
                    DeleteMessageOnServer(hostname, port, useSsl, username, password, i);
                else
                    Console.WriteLine("Can't delete");
                //                SendTicketResponse("Doris@mpdemo.no", Email, doris.Username, doris.Password, TicketID, Subject, Fullname, Email, output);
            }
        }
        public static void DeleteMessageOnServer(string hostname, int port, bool useSsl, string username, string password, int messageNumber) {
            // The client disconnects from the server when being disposed
            using (Pop3Client client = new Pop3Client()) {
                // Connect to the server
                client.Connect(hostname, port, useSsl);

                // Authenticate ourselves towards the server
                client.Authenticate(username, password);

                // Mark the message as deleted
                // Notice that it is only MARKED as deleted
                // POP3 requires you to "commit" the changes
                // which is done by sending a QUIT command to the server
                // You can also reset all marked messages, by sending a RSET command.
                client.DeleteMessage(messageNumber);
                client.Disconnect();
                client.Dispose();
                // When a QUIT command is sent to the server, the connection between them are closed.
                // When the client is disposed, the QUIT command will be sent to the server
                // just as if you had called the Disconnect method yourself.
            }
            Console.WriteLine("Done deleting " + messageNumber);
        }
        public static List<Message> FetchAllMessages(string hostname, int port, bool useSsl, string username, string password) {
            // The client disconnects from the server when being disposed
            using (Pop3Client client = new Pop3Client()) {
                // Connect to the server
                client.Connect(hostname, port, useSsl);

                // Authenticate ourselves towards the server
                client.Authenticate(username, password);

                // Get the number of messages in the inbox
                int messageCount = client.GetMessageCount();

                // We want to download all messages
                List<Message> allMessages = new List<Message>(messageCount);

                // Messages are numbered in the interval: [1, messageCount]
                // Ergo: message numbers are 1-based.
                for (int i = 1; i <= messageCount; i++) {
                    allMessages.Add(client.GetMessage(i));
                }
                client.Disconnect();
                client.Dispose();
                // Now return the fetched messages
                return allMessages;
            }
        }


    }
}